package com.dshore.messagecenter.dao.topicqueue;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class QueueChartDao {

	@Autowired
	JdbcTemplate jdbcTemplate;

	/** 获取主题队列名称 */
	public String getTopicQueueName(String tId) {
		String sql = " select TOPIC_QUEUE_NAME from T_MD_TOPIC where ID = ?";
		return jdbcTemplate.queryForObject(sql, String.class, tId);
	}
	
	/**获取send表中的queueName集合,对应OBJ*/
	public List<String> getSendQueueNames(String topicQueueNo) {
		String sql = " select QUEUE_NAME from T_MD_TOPIC_SEND where DEL_FLAG =0 "
					+ " and QUEUE_NAME is not Null and TOPIC_QUEUE_NO = ?";
		return jdbcTemplate.queryForList(sql, String.class, topicQueueNo);
	}
	
	/**取内存数据(1704 队列, 1604 内存占用) */
	public List<Map<String, Object>> getMemoryAndDate(String[] qNames, String start, String end) {
		StringBuilder sql = new StringBuilder("select * from ("
				+ " select DISTINCT KPI_TIME, KPI_VALUE from "
				+ " (select TO_CHAR ( CREATED_TIME,'hh24:mi') AS KPI_TIME, NVL (SUM(KPI_VALUE), 0) AS KPI_VALUE "
				+ "	from T_MR_KPI_RECORD_CURRENT "
				+ "	where OBJ_TYPE = (select DICT_CODE from T_SYS_DICT where DICT_TYPE = 'obj_type' and DICT_NAME = '队列' ) " 
				+ "	and KPI_CODE = (select DICT_CODE from T_SYS_DICT where DICT_TYPE = 'kpi_type' and DICT_NAME = '内存占用' )  "
				+ " and OBJ in ( ");
		for(int i=0; i<qNames.length;i++) {
			sql.append(" ? ");
			if(i != qNames.length-1) {
				sql.append(" , ");
			}
		}
		sql.append(" ) " + 
			" and TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >= ? " + 
			" and TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <= ? " + 
			" GROUP BY CREATED_TIME " + 
			" ORDER BY CREATED_TIME desc " + 
			" ) " +
			"ORDER BY KPI_TIME " +
			")where ROWNUM <= 7 ");
//		System.out.println("查询队列数据sql: " + sql.toString());
//		System.out.println("qNames集合: " + Arrays.toString(qNames));
		//若传入list参数需要转为数组
		String[] params = new String[qNames.length + 2];
		// 赋值给新数组
		for(int i=0; i<qNames.length;i++) {
			params[i] = qNames[i];
		}
		params[params.length-2] = start;
		params[params.length-1] = end;
//		System.out.println("params: " + Arrays.toString(params));
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), params);
		return list;
	}

	/**获取磁盘读取(I)数据(1704 队列, 1606 磁盘读取) */
	public List<Map<String, Object>> getDiskReads(String[] qNames, String start, String end) {
//		System.out.println("磁盘qNames参数: " + Arrays.toString(qNames));
		StringBuilder sql = new StringBuilder("select * from ("
				+ " select DISTINCT KPI_TIME, KPI_VALUE from "
				+ " (select TO_CHAR ( CREATED_TIME,'hh24:mi') AS KPI_TIME, NVL (SUM(KPI_VALUE), 0) AS KPI_VALUE "
				+ "	from T_MR_KPI_RECORD_CURRENT "
				+ "	where OBJ_TYPE = (select DICT_CODE from T_SYS_DICT where DICT_TYPE = 'obj_type' and DICT_NAME = '队列' ) " 
				+ "	and KPI_CODE = (select DICT_CODE from T_SYS_DICT where DICT_TYPE = 'kpi_type' and DICT_NAME = '磁盘读取' )  "
				+ " and OBJ in ( ");
		
		for(int i=0; i<qNames.length;i++) {
			sql.append(" ? ");
			if(i != qNames.length-1) {
				sql.append(" , ");
			}
		}
		sql.append(" ) " + 
			" and TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >= ? " + 
			" and TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <= ? " + 
			" GROUP BY CREATED_TIME " + 
			" ORDER BY CREATED_TIME desc " + 
			" ) " +
			"ORDER BY KPI_TIME " +
			")where ROWNUM <= 7 ");
		String[] params = new String[qNames.length + 2];
		for(int i=0; i<qNames.length;i++) {
			params[i] = qNames[i];
		}
		params[params.length-2] = start;
		params[params.length-1] = end;
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), params);
		return list;
	}

	/**获取磁盘写入(O)数据(1704 队列, 1607 磁盘写入)*/
	public List<Map<String, Object>> getDiskWrites(String[] qNames, String start, String end) {
		StringBuilder sql = new StringBuilder("select * from ("
				+ " select DISTINCT KPI_TIME, KPI_VALUE from "
				+ " (select TO_CHAR ( CREATED_TIME,'hh24:mi') AS KPI_TIME, NVL (SUM(KPI_VALUE), 0) AS KPI_VALUE "
				+ "	from T_MR_KPI_RECORD_CURRENT "
				+ "	where OBJ_TYPE = (select DICT_CODE from T_SYS_DICT where DICT_TYPE = 'obj_type' and DICT_NAME = '队列' ) " 
				+ "	and KPI_CODE = (select DICT_CODE from T_SYS_DICT where DICT_TYPE = 'kpi_type' and DICT_NAME = '磁盘写入' )  "
				+ " and OBJ in ( ");
		for(int i=0; i<qNames.length;i++) {
			sql.append(" ? ");
			if(i != qNames.length-1) {
				sql.append(" , ");
			}
		}
		sql.append(" ) " + 
			" and TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >= ? " + 
			" and TO_CHAR(CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <= ? " + 
			" GROUP BY CREATED_TIME " + 
			" ORDER BY CREATED_TIME desc " + 
			" ) " +
			"ORDER BY KPI_TIME " +
			")where ROWNUM <= 7 ");
		String[] params = new String[qNames.length + 2];
		for(int i=0; i<qNames.length;i++) {
			params[i] = qNames[i];
		}
		params[params.length-2] = start;
		params[params.length-1] = end;
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), params);
		return list;
	}
	
	/** 获取消息发送延时数据集合(1701 主题队列、 1602 (消息发送延迟：单位毫秒) ) */
	public List<Map<String, Object>> getMsgSDAndTime(String topicQueueNo, String start, String end) {
		String sql = "SELECT * FROM( " + 
				"	SELECT DISTINCT KPI_TIME,KPI_VALUE " + 
				"		FROM " + 
				"			( " + 
				"				SELECT TO_CHAR (CREATED_TIME, 'hh24:mi') AS KPI_TIME, " + 
				"					NVL (SUM(KPI_VALUE), 0) AS KPI_VALUE " + 
				"				FROM T_MR_KPI_RECORD_CURRENT " + 
				"				WHERE " + 
				"					OBJ_TYPE = ( SELECT DICT_CODE FROM T_SYS_DICT WHERE DICT_TYPE = 'obj_type' AND DICT_NAME = '主题队列' ) " + 
				"				AND KPI_CODE = ( SELECT DICT_CODE FROM T_SYS_DICT WHERE DICT_TYPE = 'kpi_type' AND DICT_NAME = '消息发送延迟' ) " + 
				"				AND OBJ = ? " + 
				"				AND TO_CHAR (CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >= ? " + 
				"				AND TO_CHAR (CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <= ? " + 
				"				GROUP BY CREATED_TIME  " + 
				"				ORDER BY CREATED_TIME DESC " + 
				"			) " + 
				"		ORDER BY KPI_TIME " + 
				"	) " + 
				"WHERE ROWNUM <= 7 ";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, topicQueueNo, start, end);
//		System.out.println("Dao层查询的延时数据list结果: " + list);
		return list;
	}

	/** 获取消息发送延时数据集合(1701 主题队列、 1601 (消息流量：条) ) */
	public List<Map<String, Object>> getMsgFlowAndTime(String topicQueueNo, String start, String end) {
		String sql = "SELECT * FROM( " + 
				"	SELECT DISTINCT KPI_TIME,KPI_VALUE " + 
				"		FROM " + 
				"			( " + 
				"				SELECT TO_CHAR (CREATED_TIME, 'hh24:mi') AS KPI_TIME, " + 
				"					NVL (SUM(KPI_VALUE), 0) AS KPI_VALUE " + 
				"				FROM T_MR_KPI_RECORD_CURRENT " + 
				"				WHERE " + 
				"					OBJ_TYPE = ( SELECT DICT_CODE FROM T_SYS_DICT WHERE DICT_TYPE = 'obj_type' AND DICT_NAME = '主题队列' ) " + 
				"				AND KPI_CODE = ( SELECT DICT_CODE FROM T_SYS_DICT WHERE DICT_TYPE = 'kpi_type' AND DICT_NAME = '消息流量' ) " + 
				"				AND OBJ = ? " + 
				"				AND TO_CHAR (CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') >= ? " + 
				"				AND TO_CHAR (CREATED_TIME,'yyyy-mm-dd hh24:mi:ss') <= ? " + 
				"				GROUP BY CREATED_TIME  " + 
				"				ORDER BY CREATED_TIME DESC " + 
				"			) " + 
				"		ORDER BY KPI_TIME " + 
				"	) " + 
				"WHERE ROWNUM <= 7 ";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, topicQueueNo, start, end);
//		System.out.println("Dao层查询的消息流量list结果: " + list);
		return list;
	}
	

	

}
